import QUANTAXIS as QA
from indicators import *

import os
import tushare as ts
import pandas as pd
import numpy as np
import time
import statsmodels.api as sm
from sklearn.decomposition import PCA
from sklearn.linear_model import Lasso, ElasticNet, RidgeCV, Ridge
from sklearn.ensemble import RandomForestRegressor
from sklearn.svm import SVR
from sklearn.model_selection import train_test_split
from sklearn.metrics import mean_squared_error
# from xgboost import XGBRegressor
# import lightgbm as lgb
# pd.set_option('max_rows', 10000)
# pd.set_option('max_columns', 10000)

def get_month_factors(data, date, date_trade_datetime, listedAShares, month=1):
    stock_today = data.select_day(date_trade_datetime)
    date_start = QA.QA_util_get_recent_months(date, -month)
    date_month_day_1 = QA.QA_util_get_next_trade_date(date_start)
    # date_month_day_1 = QA.QA_util_get_month_day_1(date_start)  # 获取本月1日
    # while not QA.QA_util_is_trade(date_month_day_1, '000001', CLIENT):
    #     date_month_day_1 = QA.QA_util_get_next_trade_date(date_month_day_1)
    stock_month_day_1 = data.select_day(pd.to_datetime(date_month_day_1))
    rtn = (stock_today.CLOSE - stock_month_day_1.pre_close) / stock_month_day_1.pre_close

    stock_month_1 = data.select_time(start=pd.to_datetime(date_month_day_1), end=pd.to_datetime(date))
    series_w_gt_return_1 = stock_month_1.price_chg * stock_month_1.volume * 10000 / listedAShares
    w_gt_return = series_w_gt_return_1.groupby(level=1).apply(lambda x: x.mean())
    # exp_w_gt_return = (series_w_gt_return_1 * np.exp(-stock_month_1.trade_day_count / 4)).groupby(level=1).apply(
    #     lambda x: x.mean())
    exp_w_gt_return = 0
    std = stock_month_1.price_chg.groupby(level=1).apply(lambda x: x.std())
    stock_month_1_ls = pd.DataFrame({'hsl': stock_month_1.volume * 100 / listedAShares,
                                     'filter': stock_month_1.limit_and_stopped})
    stock_month_1_filter = stock_month_1_ls[stock_month_1_ls['filter'] == False]
    turn = stock_month_1_filter['hsl'].groupby(level=1).apply(lambda x: x.mean())

    # df_up = stock_month_1.data[stock_month_1.price_chg > 0]
    # psy = df_up.shape[0] / stock_month_1.data.shape[0]

    return pd.DataFrame({'return': rtn,
                         'w_gt_return': w_gt_return,
                         'exp_w_gt_return': exp_w_gt_return,
                         'std': std,
                         # 'psy': psy,
                         'turn': turn})


t = time.time()

start_date = '2020-03-01'
# start_date = '2019-09-01'
end_date = '2020-05-30'
code = '000651'
month_data = pd.date_range(
    '1/1/1996',
    '12/31/2023',
    freq='M'
).astype(str).tolist()
Q_data = pd.date_range(
    '1/1/1996',
    '12/31/2023',
    freq='Q-MAR'
).astype(str).tolist()
df_columns = [
        # 'date',
        # 'code',
        # 总市值 = 当前股价 * 总股本（238）
        'EP',       # 估值 EP 净利润（TTM）（276）/总市值 1
        'EPcut',    # 估值 EPcut 扣除非经常性损益后净利润（TTM）（206）/总市值 1
        'BP',       # 估值 BP 净资产/总市值 1 也等于 每股股价 / 每股净资产（004）
        'SP',       # 估值 SP 营业收入（TTM）（074 ? 283）/总市值 1
        'NCFP',     # 估值 NCFP 净现金流（TTM）/总市值 1 =
        'OCFP',     # 估值 OCFP 经营性现金流（TTM）/总市值 1 = 219每股经营性现金流 / 总股本（238）
        # 'DP',       # 估值 DP 近 12 个月现金红利（按除息日计）/总市值 1
        'G_PE',     # 估值 G/PE 净利润（TTM）同比增长率(184)/PE_TTM 1
        'Sales_G_q',   # 成长 Sales_G_q 营业收入（最新财报，YTD）同比增长率(183) 1
        'Profit_G_q',  # 成长 Profit_G_q 净利润（最新财报，YTD）同比增长率(184) 1
        # 'OCF_G_q',     # 成长 OCF_G_q 经营性现金流（最新财报，YTD）同比增长率 1
                       # = （本期经营活动现金净流量(225)-上期经营活动现金净流量）/上期经营活动现金净流量*100%
        'ROE_G_q',     # 成长 ROE_G_q ROE（最新财报，YTD）(006与上期相比)同比增长率 1
        'ROE_q',       # 财务质量 ROE_q ROE（最新财报，YTD）(006) 1
        'ROE_ttm',     # 财务质量 ROE_ttm ROE（最新财报，TTM）（006） 1
        'ROA_q',       # 财务质量 ROA_q ROA（最新财报，YTD）1 资产回报率= 净利润（276）/总资产（040）
        'ROA_ttm',     # 财务质量 ROA_ttm ROA（最新财报，TTM） 1
        'grossprofitmargin_q',       # 财务质量 grossprofitmargin_q 毛利率（最新财报，YTD）（202） 1
        # 'grossprofitmargin_ttm',     # 财务质量 grossprofitmargin_ttm 毛利率（最新财报，TTM） 1
        'profitmargin_q',            # 财务质量 profitmargin_q 扣除非经常性损益后净利润率（最新财报，YTD）（206） 1
        'profitmargin_ttm',          # 财务质量 profitmargin_ttm 扣除非经常性损益后净利润率（最新财报，TTM） 1
        'assetturnover_q',           # 财务质量 assetturnover_q 资产周转率（最新财报，YTD）（175） 1
        # 'assetturnover_ttm',         # 财务质量 assetturnover_ttm 资产周转率（最新财报，TTM） 1
        'operationcashflowratio_q',  # 财务质量 operationcashflowratio_q 经营性现金流/净利润（最新财报，YTD）（228） 1
        'operationcashflowratio_ttm',  # 财务质量 operationcashflowratio_ttm 经营性现金流/净利润（最新财报，TTM） 1
        'financial_leverage',    # 杠杆 financial_leverage 总资产（040）/净资产 -1 净资产 = 每股净资产（004） * 总股本（238）
        'debtequityratio',       # 杠杆 debtequityratio 非流动负债（062）/净资产 -1
        'cashratio',             # 杠杆 cashratio 现金比率（161） 1
        'currentratio',          # 杠杆 currentratio 流动比率（159） 1
        'ln_capital',     # 市值 ln_capital 总市值取对数
        # 'HAlpha',         # 动量反转 HAlpha 个股 60 个月收益与上证综指回归的截距项 -1
        'return_1',       # 动量反转 return_Nm 个股最近 N 个月收益率，N=1 -1
        'return_3',       # 动量反转 return_Nm 个股最近 N 个月收益率，N=3 -1
        'return_6',       # 动量反转 return_Nm 个股最近 N 个月收益率，N=6 -1
        'return_12',      # 动量反转 return_Nm 个股最近 N 个月收益率，N=12 -1
        'w_gt_return_1',  # 动量反转 w gt_return_Nm 个股最近 N 个月内用每日换手率乘以每日收益率求算术平均值，N=1 -1
        'w_gt_return_3',  # 动量反转 w gt_return_Nm 个股最近 N 个月内用每日换手率乘以每日收益率求算术平均值，N=3 -1
        'w_gt_return_6',  # 动量反转 w gt_return_Nm 个股最近 N 个月内用每日换手率乘以每日收益率求算术平均值，N=6 -1
        'w_gt_return_12', # 动量反转 w gt_return_Nm 个股最近 N 个月内用每日换手率乘以每日收益率求算术平均值，N=12 -1
        # 'exp_w_gt_return_1',   # 动量反转 exp_w gt_return_Nm 个股最近 N 个月内用每日换手率乘以函数 exp(-x_i/N/4)
        #                        # 再乘以每日收益率求算术平均值，x_i为该日距离截面日的交易日的个数，N=1，3，6，12 -1
        # 'exp_w_gt_return_3',
        # 'exp_w_gt_return_6',
        # 'exp_w_gt_return_12',
        # 'std_FF3factor_1',  # 波动率 std_FF3factor_Nm 特质波动率——个股最近 N个月内用日频收益率对 Fama French
        #                      # 三因子回归的残差的标准差，N=1，3，6，12 -1
        # 'std_FF3factor_3',
        # 'std_FF3factor_6',
        # 'std_FF3factor_12',
        'std_1',       # 波动率 std_Nm 个股最近 N个月的日收益率序列标准差，N=1 -1
        'std_3',       # 波动率 std_Nm 个股最近 N个月的日收益率序列标准差，N=3 -1
        'std_6',       # 波动率 std_Nm 个股最近 N个月的日收益率序列标准差，N=6 -1
        'std_12',      # 波动率 std_Nm 个股最近 N个月的日收益率序列标准差，N=12 -1
        'ln_price',    # 股价 ln_price 股价取对数 -1
        # 'beta',        # beta beta 个股 60 个月收益与上证综指回归的 beta -1
        'turn_1',       # 换手率 turn_Nm 个股最近 N个月内日均换手率（剔除停牌、涨跌停的交易日），N=1 -1
        'turn_3',       # 换手率 turn_Nm 个股最近 N个月内日均换手率（剔除停牌、涨跌停的交易日），N=3 -1
        'turn_6',       # 换手率 turn_Nm 个股最近 N个月内日均换手率（剔除停牌、涨跌停的交易日），N=6 -1
        'turn_12',      # 换手率 turn_Nm 个股最近 N个月内日均换手率（剔除停牌、涨跌停的交易日），N=12 -1
        # 'bias_turn_1',  # 换手率 bias_turn_Nm个股最近N个月内日均换手率除以最近2年内日均换手率（剔除停牌、涨跌停的交易日）再减去1，N=1
        # 'bias_turn_3',  # 换手率 bias_turn_Nm个股最近N个月内日均换手率除以最近2年内日均换手率（剔除停牌、涨跌停的交易日）再减去1，N=3
        # 'bias_turn_6',  # 换手率 bias_turn_Nm个股最近N个月内日均换手率除以最近2年内日均换手率（剔除停牌、涨跌停的交易日）再减去1，N=6
        # 'bias_turn_12', # 换手率 bias_turn_Nm个股最近N个月内日均换手率除以最近2年内日均换手率（剔除停牌、涨跌停的交易日）再减去1，N=12
        # 'rating_average',       # 情绪 rating_average w ind 评级的平均值 1
        # 'rating_change',        # 情绪 rating_change w ind 评级（上调家数-下调家数）/总数 1
        # 'rating_targetprice',   # 情绪 rating_targetprice w ind 一致目标价/现价-1 1
        # 'holder_avgpctchange',  # 股东 holder_avgpctchange 户均持股比例的同比增长率 1
                                # 可以替换成股东户数（242）增长率 -1
        'MACD',  # 技术 MACD 经典技术指标（释义可参考百度百科），长周期取 30 日，短周期取 10 日，计算 DEA 均线的周期（中周期）取 15日 -1
        'DEA',       # 技术 DEA -1
        'DIF',       # 技术 DIF -1
        'RSI',       # 技术 RSI 经典技术指标，周期取 20 日 -1
        # 'PSY',       # 技术 PSY 经典技术指标，周期取 20 日 -1
        'BIAS'       # 技术 BIAS 经典技术指标，周期取 20 日 -1
        ]
# print(ts.__version__)
ts_token = '17056d23a59ab71cb979c6a30185e092aba605c4544dac900a3eb7f8'
ts.set_token(ts_token)
pro = ts.pro_api()
# if os.path.exists('stocks.csv'):
#     data = pd.read_csv('stocks.csv')
# else:
data = pro.stock_basic(exchange='', list_status='L', fields='symbol,name,area,industry,list_date')
# data.to_csv('stocks.csv', encoding='utf_8_sig')
data = data[data.symbol.str.startswith('000') | data.symbol.str.startswith('600')]
all = data.rename(columns={'symbol': 'code'})
# all = data.set_index(['code'])
# all.to_csv('all.csv', encoding='utf_8_sig')
all = all[~all.name.str.contains('ST')]
# all.to_csv('all-ST.csv', encoding='utf_8_sig')
# print(all.head())
if os.path.exists('D:\\PythonPro\\QUANTAXIS\\AI\\hs300.csv'):
    hs300 = pd.read_csv('D:\\PythonPro\\QUANTAXIS\\AI\\hs300.csv')
else:
    hs300 = ts.get_hs300s()
if os.path.exists('D:\\PythonPro\\QUANTAXIS\\AI\\zz500.csv'):
    zz500 = pd.read_csv('D:\\PythonPro\\QUANTAXIS\\AI\\zz500.csv')
else:
    zz500 = ts.get_zz500s()
# hs300.to_csv('hs300.csv', encoding='utf_8_sig')
# zz500.to_csv('zz500.csv', encoding='utf_8_sig')
# all = pd.concat([hs300, zz500])
hs300_w = hs300[['code', 'weight']]
hs300_w.set_index('code', inplace=True)
# codes = all['code'].to_list()  # 沪深所有
# codes = hs300['code'].to_list()  # 沪深300
# codes = ['000001', '000002']
codes = pd.concat([hs300, zz500])['code'].to_list()  # zz500 + hs300

# df_sw = ts.get_industry_classified(standard='sw')
# df_sw = df_sw.drop_duplicates(subset='code', keep='first')
# df_sw.set_index(['code'], inplace=True)
# # df_hy = df_sw.loc[codes]  # 如果在codes中不存在该索引，未来版本pandas会报错，使用reindex函数
# # df_hy = df_sw.loc[df_sw.index.intersection(codes)]  # 在行业数据中不存在的code将会被屏蔽
# df_hy = df_sw.reindex(codes)
# df_hy.fillna(0, inplace=True)
df_industry = pd.read_csv('D:\\PythonPro\\QUANTAXIS\\AI\\industry.csv', dtype=str)
df_hy = df_industry[['code', 'name', 'industry']]
df_hy = df_hy.set_index(['code'])
# print(df_idt.head())
# df_idt.to_csv('industry-onehot.csv', encoding='utf_8_sig')
# print(df_hy[['name']].head())
# df_hy.to_csv('industry-codes.csv', encoding='utf_8_sig')
# df_sw.to_csv('industry.csv', encoding='utf_8_sig')
# industries = set(df_hy.c_name)
# print(len(industries))
hs300_w = pd.concat([hs300_w, df_hy.reindex(hs300_w.index)], axis=1)
hs300_industry_w = hs300_w.groupby('industry').sum()
# print(hs300_industry_w.head())
# hs300_industry_w.to_csv('hs300_industry_weight.csv', encoding='utf_8_sig')

print('已获取列表：%.4f' % (time.time() - t))
# print(codes)

# data_w1 = QA.QA_fetch_stock_week_adv([code], '2018-07-01', '2019-9-30')
# data_d = QA.QA_fetch_stock_day_adv(codes, start_date, end_date)
data_d = QA.QA_fetch_stock_day_adv(codes, QA.QA_util_get_previous_year_month(start_date), QA.QA_util_get_recent_months(end_date, months=2))
data_d = data_d.to_qfq()
# data_d.data.to_csv('test.csv')
# data_w = data_d.resample('w')
# data_m = data_d.resample('M')
# data_w1 = data_w1.to_qfq()
# data_w.to_csv('data_w.csv')
# data_w1.data.to_csv('data_w1.csv')

# 数据库中可能存在没有的code，这里以数据库中的code为准
codes = list(data_d.code)
if len(data_d.code) != df_hy.shape[0]:
    df_hy = df_hy.reindex(codes)

# print(len(data_d.code))
# print(df_hy.shape)

# res = QA.QA_fetch_financial_report([code], ['2019-09-30'])
res = QA.QA_fetch_financial_report_adv(codes, start=QA.QA_util_get_recent_months(QA.QA_util_get_previous_year(start_date), months=-6), end=end_date)
# print(res.data)
# res.data.to_csv('res.csv')

data300 = QA.QA_fetch_index_day_adv('000300', start_date, QA.QA_util_get_recent_months(end_date, months=2))

print('已获取数据库数据：%04f' % (time.time() - t))

# date = '2019-9-30'
# close = data_m[date][code]['close']
# totalCapital = res.totalCapital[pd.to_datetime(date)][code]
# netProfit = res.netProfitsBelongToParentCompanyOwner[pd.to_datetime(date)][code]
# netProfit = netProfit * 4 / 3
# zsz = 62.73 * totalCapital
# EP = netProfit / zsz
# print('PE:%f' % (zsz / netProfit))
# print('operatingRevenue：%f' % res.operatingRevenue[pd.to_datetime(date)][code])
# res.to_csv('res.csv')

series = pd.Series(data=month_data, index=pd.to_datetime(month_data), name='date')
timerange = series.loc[start_date: end_date].tolist()
df = pd.DataFrame(columns=df_columns)
df_class = pd.DataFrame()

date_financial_datetime = None
pre_netCashFlowPerShare = None
pre_ROE = None
index = 0
pca = PCA(n_components=len(df_columns))
for date in timerange:
    print('Time:%s' % date)
    date_next_month_last = QA.QA_util_get_real_date(QA.QA_util_get_recent_months(date, 1), towards=-1)
    date_trade = QA.QA_util_get_real_date(date, towards=-1)
    date_next_month_first = QA.QA_util_get_real_date(QA.QA_util_get_month_day_1(date_next_month_last), towards=1)
    date_next_next_month_first = QA.QA_util_get_real_date(QA.QA_util_get_month_day_1(QA.QA_util_get_recent_months(date, 2)), towards=1)

    date_financial = QA.QA_util_get_real_fina_date(date)
    print('The real financial date is %s' % date_financial)
    if date_financial in Q_data:
        # 计算同比季报
        date_financial_datetime = pd.to_datetime(date_financial)
        date_financial_previous = Q_data[Q_data.index(date_financial) - 4]
        date_financial_previous_datetime = pd.to_datetime(date_financial_previous)
        # 计算TTM
        ttm_list = QA.QA_util_get_TTM_date_list(date)
        # print(ttm_list)
        date_financial_ttm = [pd.to_datetime(x) for x in ttm_list]
    if date_financial_datetime is None:
        print('%s未计算，因为不在Q期内' % date)
        continue

    date_trade_datetime = pd.to_datetime(date_trade)
    date_trade_next_month_datetime = pd.to_datetime(date_next_month_last)
    date_trade_next_month_first_datetime = pd.to_datetime(date_next_month_first)
    date_trade_next_next_month_first_datetime = pd.to_datetime(date_next_next_month_first)

    stock_today = data_d.select_day(date_trade_datetime)
    stock_next_month_last = data_d.select_day(date_trade_next_month_datetime)
    stock_next_month_first = data_d.select_day(date_trade_next_month_first_datetime)
    stock_next_next_month_first = data_d.select_day(date_trade_next_next_month_first_datetime)
    codes = list(set(stock_today.data.index).intersection(list(stock_next_month_last.data.index),
                                                          list(stock_next_month_first.data.index),
                                                          list(stock_next_next_month_first.data.index)))
    print('截面%s的代码共%d个' % (date_trade, len(codes)))  # 每个截面的代码个数是不一样的，将codes放在截面里，便于提取

    index_today = data300.select_day(date_trade_datetime)
    index_next_month = data300.select_day(date_trade_next_month_datetime)
    index_return = (index_next_month.CLOSE - index_today.CLOSE) / index_today.CLOSE
    index_return = index_return.iloc[0]

    # stock_today = data_d.data.loc[date_trade_datetime]
    # stock_today['CLOSE'] = stock_today.close.fillna(stock_today.preclose)
    # stock_next_month = data_d.data.loc[date_trade_next_month_datetime]
    # stock_next_month['CLOSE'] = stock_next_month.close.fillna(stock_next_month.preclose)
    forward_return = (stock_next_next_month_first.CLOSE - stock_next_month_first.CLOSE) / stock_next_month_first.CLOSE
    label_return = (stock_next_month_last.CLOSE - stock_today.CLOSE) / stock_today.CLOSE
    label = label_return - index_return

    close = stock_today.CLOSE
    res_data = res.data.loc[date_financial_datetime]
    res_data_pre = res.data.loc[date_financial_previous_datetime]
    res_data_ttm = res.data.loc[date_financial_ttm].drop(['code', 'report_date'], axis=1)
    res_data_ttm = res_data_ttm.reset_index().groupby('code').sum()
    # print(res_data.head())
    # print(res_data_ttm.head())
    # res_data_ttm.to_csv('ttm.csv')
    # print('out put ttm')
    
    # if update_Q:
    totalCapital = res_data.totalCapital + 1  # 238 总股本
    totalValue = close * totalCapital
    netAssetsPerShare = res_data.netAssetsPerShare  # 每股净资产（004）
    netAssets = netAssetsPerShare * totalCapital  # 净资产
    ROE = res_data.netAssetsPerShare  # （006）
    totalAssets = res_data.totalAssets  # 040 总资产
    totalNonCurrentLiabilities = res_data.totalNonCurrentLiabilities  # 062
    netCashFlows = res_data.netCashFlowsFromOperatingActivities  # 107经营活动产生的现金流量净额
    netProfitGrowthRate = res_data.netProfitGrowthRate  # 184
    cutProfit = res_data.netProfitAfterExtraordinaryGainsAndLosses  # 206
    netCashFlowPerShare = res_data.netCashFlowPerShare  # 225
    operatingCashFlowPerShare = res_data.operatingCashFlowPerShare  # 219
    listedAShares = res_data.listedAShares  # 239 A股流通股本
    lastIncome = res_data.lastYearOperatingIncome  # 283
    netProfit = res_data.netProfitsBelongToParentCompanyOwner  # 096
    totalOwnersEquity = res_data.totalOwnersEquity  # 072 所有者权益
    operatingRevenue = res_data.operatingRevenue  # 074 营业收入

    # TTM
    operatingRevenue_TTM = res_data_ttm.operatingRevenueSingle  # 230 营业收入
    operatingProfit_TTM = res_data_ttm.operatingProfitSingle  # 231 营业利润
    netProfit_TTM = res_data_ttm.netProfitBelongingToTheOwnerOfTheParentCompanySingle  # 232
    cutProfit_TTM = res_data_ttm.netProfitAfterExtraordinaryGainsAndLossesSingle  # 233
    netCashFlowsFromOperatingActivities_TTM = res_data_ttm.netCashFlowsFromOperatingActivitiesSingle  # 234
    netCashFlowsFromInvestingActivities_TTM = res_data_ttm.netCashFlowsFromInvestingActivitiesSingle  # 235
    netCashFlowsFromFinancingActivities_TTM = res_data_ttm.netCashFlowsFromFinancingActivitiesSingle  # 236
    netIncreaseInCashAndCashEquivalents_TTM = res_data_ttm.netIncreaseInCashAndCashEquivalentsSingle  # 237
    # TODO 净现金流目前使用三项相加，不知道对错
    netCashFlows_TTM = netCashFlowsFromOperatingActivities_TTM + netCashFlowsFromInvestingActivities_TTM + netCashFlowsFromFinancingActivities_TTM

    pre_ROE = res_data_pre.netAssetsPerShare  # （006）
    pre_netCashFlowPerShare = res_data_pre.netCashFlowPerShare  # 225
    # netProfit = netProfit * 4 / 3

    print('已获取财务数据：%04f' % (time.time() - t))

    ln_price = np.log(stock_today.CLOSE)
    df_macd = data_d.add_func(QA.QA_indicator_MACD, short=10, long=30, mid=15)
    df_macd_today_macd = df_macd.MACD[date_trade_datetime]
    df_macd_today_dif = df_macd.DIF[date_trade_datetime]
    df_macd_today_dea = df_macd.DEA[date_trade_datetime]
    df_rsi = data_d.add_func(QA.QA_indicator_RSI, N1=20)
    # df_rsi_today = df_rsi.loc[(date_trade_datetime, slice(None)), :]
    df_rsi_today = df_rsi.RSI1[date_trade_datetime]
    df_bias = data_d.add_func(QA.QA_indicator_BIAS, N1=20, N2=20, N3=20)
    df_bias_today = df_bias.BIAS1[date_trade_datetime]

    print('已获取指标数据：%04f' % (time.time() - t))

    df_1 = get_month_factors(data_d, date, date_trade_datetime, listedAShares, month=1)
    df_3 = get_month_factors(data_d, date, date_trade_datetime, listedAShares, month=3)
    df_6 = get_month_factors(data_d, date, date_trade_datetime, listedAShares, month=6)
    df_12 = get_month_factors(data_d, date, date_trade_datetime, listedAShares, month=12)

    print('已获取月度数据：%04f' % (time.time() - t))

    df_factors = pd.DataFrame({
                           # 'date': date,
                           # 'code': data_d.code,
                           # 'name': df_hy.name[codes],
                           # 'industry': df_hy.c_name[codes],
                           # 'label': label,
                           # 'label_return': label_return,
                           'EP': netProfit_TTM / totalValue,
                           'EPcut': cutProfit_TTM / totalValue,
                           'BP': close / netAssetsPerShare,
                           'SP': operatingRevenue_TTM / totalValue,
                           'NCFP': netCashFlows_TTM / totalValue,
                           'OCFP': netCashFlowsFromOperatingActivities_TTM / totalCapital,
                           # 'DP': 0,
                           'G_PE': netProfitGrowthRate * totalValue / netProfit,
                           'Sales_G_q': res.data.operatingIncomeGrowth[date_financial_datetime],
                           'Profit_G_q': netProfitGrowthRate,
                           # 'OCF_G_q': (netCashFlowPerShare - pre_netCashFlowPerShare) / pre_netCashFlowPerShare,  # 可能除0
                           'ROE_G_q': (ROE - pre_ROE) / pre_ROE,
                           'ROE_q': ROE,
                           'ROE_ttm': netProfit_TTM / totalOwnersEquity,
                           'ROA_q': netProfit / totalAssets,
                           'ROA_ttm': netProfit_TTM / totalAssets,
                           'grossprofitmargin_q': res.data.rateOfReturnOnGrossProfitFromSales[date_financial_datetime],
                           # 'grossprofitmargin_ttm': 0,
                           'profitmargin_q': cutProfit / operatingRevenue,
                           'profitmargin_ttm': cutProfit_TTM / operatingRevenue_TTM,
                           'assetturnover_q': res.data.turnoverRatioOfTotalAssets[date_financial_datetime],
                           # 'assetturnover_ttm': 0,
                           'operationcashflowratio_q': res.data.cashFlowRateAndNetProfitRatioOfOperatingActivities[date_financial_datetime],
                           'operationcashflowratio_ttm': netCashFlowsFromOperatingActivities_TTM  / netProfit_TTM,
                           'financial_leverage': totalAssets / netAssets,
                           'debtequityratio': totalNonCurrentLiabilities / netAssets,
                           'cashratio': res.data.cashRatio[date_financial_datetime],
                           'currentratio': res.data.currentRatio[date_financial_datetime],
                           'ln_capital': np.log(totalValue),
                           # 'HAlpha': 0,
                           'ln_price': ln_price,
                           # 'PSY': df_1['psy'],
                           'return_1': df_1['return'],
                           'w_gt_return_1': df_1['w_gt_return'],
                           # 'exp_w_gt_return_1': df_1['exp_w_gt_return'],
                           'std_1': df_1['std'],
                           'turn_1': df_1['turn'],
                           'return_3': df_3['return'],
                           'w_gt_return_3': df_3['w_gt_return'],
                           # 'exp_w_gt_return_3': df_3['exp_w_gt_return'],
                           'std_3': df_3['std'],
                           'turn_3': df_3['turn'],
                           'return_6': df_6['return'],
                           'w_gt_return_6': df_6['w_gt_return'],
                           # 'exp_w_gt_return_6': df_6['exp_w_gt_return'],
                           'std_6': df_6['std'],
                           'turn_6': df_6['turn'],
                           'return_12': df_12['return'],
                           'w_gt_return_12': df_12['w_gt_return'],
                           # 'exp_w_gt_return_12': df_12['exp_w_gt_return'],
                           'std_12': df_12['std'],
                           'turn_12': df_12['turn'],
                           'MACD': df_macd_today_macd,
                           'DEA': df_macd_today_dea,
                           'DIF': df_macd_today_dif,
                           'RSI': df_rsi_today,
                           'BIAS': df_bias_today}, index=codes)
    df_tmp = pd.DataFrame(index=codes)

    # df_tmp = df_tmp.dropna(subset=['label'])  # 删除标签为空的行, 加入后添加code列时会无法对齐, 以后想办法处理
    # 去极值 MAD法
    df_factors = df_factors.apply(QA.QA_util_median_limit)
    # df_tmp['code'] = codes

    # 按行业进行均值填充nan
    df_factors['industry'] = df_hy.industry[codes]
    df_factors = df_factors.groupby('industry').transform(lambda x: x.fillna(x.mean()))

    # 行业市值中性化
    df_factors = df_factors.apply(lambda x: x if x.name == 'code' else QA.QA_util_neutralization(x, df_hy=df_hy.loc[codes], total_value=df_factors['ln_capital']))

    # z-score标准化
    df_factors = df_factors.apply(lambda x: x if x.name == 'code' else (x - x.mean()) / x.std())

    # 主成分分析
    # reduced_x = pca.fit_transform(df_tmp[df_columns])
    # df_tmp[df_columns] = reduced_x

    df_tmp['date'] = date
    df_tmp['name'] = df_hy.name[codes]
    df_tmp['close'] = close
    df_tmp['label'] = label
    df_tmp['label_return'] = label_return
    df_tmp['forward_return'] = forward_return
    df_tmp['industry'] = df_hy.industry[codes]
    df_tmp.dropna(inplace=True)
    df_tmp.index.name = 'code'
    df_cate = df_tmp.sort_values(by='label', ascending=False)
    df_cate = pd.concat([df_cate, df_factors.reindex(df_cate.index)], axis=1)
    df_cate.reset_index(inplace=True)
    samples_count = int(df_cate.shape[0] * 0.3)
    df_true = df_cate.iloc[:samples_count]
    df_false = df_cate.iloc[-samples_count:]
    df_true.loc[:, 'label_cate'] = 1
    df_false.loc[:, 'label_cate'] = 0
    df_tmp = pd.concat([df_tmp, df_factors.reindex(df_tmp.index)], axis=1)
    df_tmp.reset_index(inplace=True)
    df = df.append(df_tmp, sort=False)
    df_class = df_class.append(df_true, sort=False)
    df_class = df_class.append(df_false, sort=False)
    index += 1

print(df.head(10))
df = df.set_index(['date', 'code'])
# df = df.set_index('date')
df.to_csv('data.csv', encoding='utf_8_sig')
df_class = df_class.set_index(['date', 'code'])
df_class.to_csv('data_cate.csv', encoding='utf_8_sig')